﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SQLite;

namespace WaterResources
{
    interface IWaterResourcesDb
    {
        string GetFindSql();
        string GetSaveSql();
        string GetEditSql();
        string GetDropSql();
    }
    
    class WaterResourcesDb<T>
        where T : IWaterResourcesDb,new()
    {
        protected SQLiteConnection connection;

        public WaterResourcesDb()
        {
            connection= new SQLiteConnection($"Data Source={System.Environment.CurrentDirectory}/water_resources.db");
            //connection = new SQLiteConnection($"Data Source=E:/WaterResources/water-resources/water_resources.db");
            connection.Open();
        }

        ~WaterResourcesDb()
        {
            connection.Dispose();
        }

        public IEnumerable<T> Find(string sql, object param)
        {
            return connection.Query<T>(sql, param);
        }

        public IEnumerable<T> Find()
        {
            var model = new T();
            return Find(model.GetFindSql(), new DynamicParameters());
        }
        public T FindFirst(string sql, object param)
        {
            return connection.QueryFirstOrDefault<T>(sql, param);
        }

        public T FindFirst()
        {
            var model = new T();
            return FindFirst(model.GetFindSql(), new DynamicParameters());
        }
        public int Save(string sql, T model)
        {
            if (string.IsNullOrEmpty(sql) || !sql.ToLower().StartsWith("insert") || model == null)
            {
                throw new ArgumentException("参数错误");
            }
            return connection.Execute(sql, model);
        }

        public int Save(T model)
        {
            return Save(model.GetSaveSql(), model);
        }

        public int Edit(string sql, T model)
        {
            if (string.IsNullOrEmpty(sql) || !sql.ToLower().StartsWith("update") || model == null)
            {
                throw new ArgumentException("参数错误");
            }
            return connection.Execute(sql, model);
        }

        public int Edit(T model)
        {
            return Edit(model.GetEditSql(), model);
        }

        public int Drop(string sql, int id)
        {
            if (string.IsNullOrEmpty(sql) || !sql.ToLower().StartsWith("delete") || id <= 0)
            {
                throw new ArgumentException("参数错误");
            }
            return connection.Execute(sql, new { Id = id });
        }

        public int Drop(int id)
        {
            var model = new T();
            return Drop(model.GetDropSql(), id);
        }
    }
}
